<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Rekening
 *
 * @author Massri
 */
class Application_Model_Rekening {

    //put your code here
    private $_db;

    function __construct() {
        $registry = Zend_Registry::getInstance();
        $this->_db = $registry->database;
    }

    /*
     * $member = array(
     * member_code,member_name,pin,sponsor_id, member_type_id
     * )
     */

    public function getAccounts($accNum) {

        $select = $this->_db->select();
        $select->from('accounts');
        $select->where( "account_num = '$accNum'");
        $select->order( 'account_name' );

        $stmt = $select->query();
        $result = $stmt->fetchAll();

        return $result;

    }

    public function getPersonalAcc($memberId, $where){
        $select = $this->_db->select();
        $select->from('personal_data_trans');
        $select->where( "member_id = ".$memberId." and ".$where );
        $select->order( 'trans_name' );

        $stmt = $select->query();
        $result = $stmt->fetchAll();

        return $result;
    }

    public function getPersonalAccByTransCode($memberId, $code){

        $transCode = ($code == 'PLN') ? 'PB02' : 'PB01';
        
        $sql = "select a.*
                from personal_data_trans a, trans_type b
                where member_id = ".$memberId." and a.trans_type_id = b.trans_type_id and b.trans_type_code = '$transCode'
                order by trans_name";

        $result = $this->_db->fetchAll($sql);


        return $result;
    }

    function getAccountList($accNum) {

        // Get Total
        $sql = "select count(1) as total
                from accounts where account_num = '$accNum'";

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];

        $data = array($rows);


        $sql = "select account_num
                ,account_name
                ,bank_name
                ,bank_account_no
                ,FORMAT(balance,2) as balance
                ,FORMAT(balance_save,2) as balance_save
                ,FORMAT(balance_trx,2) as balance_trx
                ,'IDR' as money
                from accounts where account_num = '$accNum' order by account_name";

        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }

    function getTotalSaldo($memberId) {

        $sql = "select account_num, account_name from accounts where member_id = $memberId";
        $resultC = $this->_db->fetchRow($sql);
        $accNum = $resultC["account_num"];


        $sql = "select FORMAT(balance, 2) as balance
                from accounts
                where member_id = $memberId";

        $resultA = $this->_db->fetchRow($sql);


        $sql = "select sum(amt1) as amt, debkred from (
                select trans_dtm, acc_num1, FORMAT(amt1,2) as amt_rp, amt1,
                notes_txt as descr,
                mutation_type as debkred
                from v_transaction where acc_num1 = '$accNum'
                ) ww group by debkred";

        $resultB = $this->_db->fetchAll($sql);
        $num = count($resultB);

        $debet = 0;
        $kredit = 0;

        if ($num > 0){

            for($k = 0;$k<$num;$k++) {
                if ($resultB[$k]['debkred'] == 'D'){
                    $debet = $resultB[$k]['amt'];
                }else{
                    $kredit = $resultB[$k]['amt'];
                }
            }
        }

        $arrDebKred = array('debet' => $debet, 'kredit' => $kredit);

        $result = array_merge($resultA, $arrDebKred, $resultC);

        return $result;

    }

    function getMutasi($accNum, $transType, $date1, $date2, $start, $limit) {

        
        $sql = "select count(1) as total, FORMAT(sum(amt1),2) as jumlah
                from v_transaction where is_display = 'Y'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                ";

        if ($accNum != ""){
            $sql .= " and acc_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];
        $total = $result['jumlah'];

        $data = array($rows, $total);

       
        $sql = "select trans_dtm, acc_num1, 
                FORMAT(if(trans_type_code in ('3001', '3008'), amt2, amt1),2) as amt_rp,
                amt1,
                notes_txt as descr,
                mutation_type as debkred,
                if(mutation_type = 'D', FORMAT(amt1,2), 0) as debit, if(mutation_type = 'K', FORMAT(amt1,2), 0) as kredit
                from v_transaction where is_display = 'Y' 
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){
            $sql .= " and acc_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        $sql .= " order by trans_dtm desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }


    function getTotalBalance($key) {

        $sql = "select balance, FORMAT(balance,0) as balance2,
                balance_trx, FORMAT(balance_trx,0) as balance_trx2
                from accounts
                where account_num = '$key'";

        $result = $this->_db->fetchRow($sql);
        return $result;
    }

    function isExistAccount($accNum){
        $sql = "select count(1) as cnt
                from accounts
                where account_num = '$accNum'";

        $row = $this->_db->fetchRow($sql);

        $result = array("cnt" => $row["cnt"]);

        return $result;
    }

    function inquiryPoint($memberId, $accountNum){
        $this->_db->beginTransaction();

        $result = array();

        try {

            $code = "";
            $msg = "";

            $sql = "select mobile_no
                    from member_contact
                    where member_id = $memberId and contact_type_id = 1";

            $result = $this->_db->fetchRow($sql);
            $mdn = $result['mobile_no'];

            $dataInput = array($accountNum, $mdn);
            $stmt = $this->_db->query("CALL Trx_InqueryPoint(?, ?, @out_code, @out_msg)", $dataInput);
            $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
            //print_r( $stmt->fetchAll() );
            $data = $stmt->fetchAll();

            $code = $data[0]['out_code'];
            $msg = $data[0]['out_msg'];

            if($code == 1){

                $result = array(
                    'code'  => (string)$code,
                    'msg'   => $msg
                );

                return $result;

            }

            $this->_db->commit();


            $result = array(
                'code'  => (string)$code,
                'msg'   => $msg
            );

            return $result;

        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $result = array(
                'code'  => '1',
                'msg'   => $errMsg
            );

            return $result;
        }
    }
    
    function withdraw($memberId, $accountNum, $amount, $bank){
        $this->_db->beginTransaction();

        $result = array();

        try {

            $dataInput = array($accountNum, $amount, $bank);
            
            
            $stmt = $this->_db->query("CALL Trx_Withdraw( ?, ?, ?, @out_code, @out_msg)", $dataInput);
            $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
            //print_r( $stmt->fetchAll() );
            $data = $stmt->fetchAll();

            $code = $data[0]['out_code'];
            $msg = $data[0]['out_msg'];
          

            if($code == 1){

                $this->_db->rollBack();

                $result = array(
                    'code'  => (string)$code,
                    'msg'   => $msg
                );

                return $result;

            }

            $this->_db->commit();


            $result = array(
                'code'  => (string)$code,
                'msg'   => $msg
            );

            

            return $result;

        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $result = array(
                'code'  => '1',
                'msg'   => $errMsg
            );

            return $result;
        }
    }

    function getAccountBank($memberId) {

        $sql = "select bank_account_no, concat('[',bank_account_no,']',' ',bank_name,' ','(',account_name,')') as bank_acc_num
                from accounts
                where member_id = $memberId 
                order by bank_account_no";



        $result = $this->_db->fetchRow($sql);

        return $result;
    }

    function getAccountBankList($memberId) {

        $sql = "select account_num, bank_account_no, concat('[',bank_account_no,']',' ',bank_name,' ','(',account_name,')') as bank_acc_num
                from accounts
                where member_id = $memberId
                order by bank_account_no";



        $result = $this->_db->fetchAll($sql);

        return $result;
    }

    function getWithdrawFee(){
        $sql = "select int_value
                from global_params
                where param_name = 'WITHDRAW_CHARGE_FEE'";

        $result = $this->_db->fetchRow($sql);
        $fee = $result['int_value'];

        return $fee;
    }

    function getMinimumBalance(){
        $sql = "select int_value
                from global_params
                where param_name = 'MIN_SALDO'";

        $result = $this->_db->fetchRow($sql);
        $fee = $result['int_value'];

        return $fee;
    }
    

    function topupPoint($memberId, $bankAccNo, $amount, $tglTrans, $remark){

        $this->_db->beginTransaction();

        $result = array();

        try {

            $code = "";
            $msg = "";

            $text = $tglTrans."#".$remark;

            $dataInput = array($bankAccNo, $amount, $text);

            $stmt = $this->_db->query("CALL Trx_Topup(?, ?, ?, @out_code, @out_msg)", $dataInput);

            $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
            $data = $stmt->fetchAll();

            $code = $data[0]['out_code'];
            $msg = $data[0]['out_msg'];

            $result = array(
                'code'  => (string)$code,
                'msg'   => $msg
            );

            if($code == 1){

                $this->_db->rollBack();

                return $result;

            }

            $this->_db->commit();

            return $result;

        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $result = array(
                'code'  => '1',
                'msg'   => $errMsg
            );

            return $result;
        }
    }


    function getBonusTransaction($accNum, $date1, $date2, $start, $limit) {

        // Get Total
        $sql = "select count(1) as total
                from trx_point_bonus where account_num2 = '$accNum'";

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];

        $data = array($rows);


        $sql = "select trx_dtm
                ,amount
                ,FORMAT(amount,2) as amount_rp
                ,notes_txt
                from trx_point_bonus where account_num2 = '$accNum'
                and STR_TO_DATE(DATE_FORMAT(trx_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        
        $sql .= " order by trx_dtm desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }
}
